﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public class CollectRequestDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public CollectRequestDAL()
        {

        }

        public void CollectRequestItem(string ppr_seq_text, IList<int> infoPprItemList, DateTime pr_date,
            string pr_name, string pr_reason,int pc_rule_seq,int ppr_type_seq,int pc_type_seq,
            int year_type_seq,int bgm_year,DateTime? req_date,Nullable<int> req_duration,Nullable<int> dur_type_seq,
            Nullable<int> dur_count_seq, int pr_emp_seq)
        {
            string strSQL = "";
            int lastSeq;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@ppr_seq_text", ppr_seq_text);
                dbManager.AddParameters(1, "@pr_date", pr_date);
                dbManager.AddParameters(2, "@pr_name", pr_name);
                dbManager.AddParameters(3, "@pr_reason", pr_reason);
                dbManager.AddParameters(4, "@pc_rule_seq", pc_rule_seq);
                dbManager.AddParameters(5, "@ppr_type_seq", ppr_type_seq);
                dbManager.AddParameters(6, "@pc_type_seq", pc_type_seq);
                dbManager.AddParameters(7, "@year_type_seq", year_type_seq);
                dbManager.AddParameters(8, "@bgm_year", bgm_year);
                dbManager.AddParameters(9, "@req_date", req_date);
                dbManager.AddParameters(10, "@req_duration", req_duration);
                dbManager.AddParameters(11, "@dur_type_seq", dur_type_seq);
                dbManager.AddParameters(12, "@dur_count_seq", dur_count_seq);
                dbManager.AddParameters(13, "@pr_emp_seq", pr_emp_seq);

                strSQL = "SELECT F_Auto_Insert_Purchase_Request_Select(@ppr_seq_text"
                    + ",@pr_date"
                    + ",@pr_name"
                    + ",@pr_reason"
                    + ",@pc_rule_seq"
                    + ",@ppr_type_seq"
                    + ",@pc_type_seq"
                    + ",@year_type_seq"
                    + ",@bgm_year"
                    + ",@req_date"
                    + ",@req_duration"
                    + ",@dur_type_seq"
                    + ",@dur_count_seq"
                    + ",@pr_emp_seq"
                    + ")";

                lastSeq =  Convert.ToInt32(dbManager.ExecuteScalar(CommandType.Text, strSQL));

                FindItemGroup(lastSeq, infoPprItemList, pr_emp_seq, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void FindItemGroup(int PrSeq, IList<int> infoPprItemList, int prEmpSeq, IDBManager dbManagerTrans)
        {
            string seq = "";
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            Nullable<int> BgmExpSeq, PcRuleSeq, ItemSeq;

            for (int i = 0; i < infoPprItemList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoPprItemList[i];
                }
                else
                {
                    seq += "," + infoPprItemList[i];
                }
            }

            try
            {
                strSQL = "SELECT BGM_EXP_SEQ, ICM_PC_RULE_SEQ, ICM_RES_ITEM_SEQ"
						+ " FROM ICM_PRE_PURCHASE_REQUEST_ITEM"
						+ " WHERE ICM_PPR_ITEM_SEQ IN (" + seq + ")"
                            + " AND ICM_PR_ITEM_SEQ IS NULL" 
						+ " GROUP BY BGM_EXP_SEQ, ICM_PC_RULE_SEQ, ICM_RES_ITEM_SEQ";
                
                ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BgmExpSeq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        PcRuleSeq = (Convert.IsDBNull(dr[i]["ICM_PC_RULE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PC_RULE_SEQ"]));
                        ItemSeq = (Convert.IsDBNull(dr[i]["ICM_RES_ITEM_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_RES_ITEM_SEQ"]));
                        InsertItem(PrSeq, seq, BgmExpSeq, PcRuleSeq, ItemSeq, prEmpSeq, dbManagerTrans);
                    }
                }
                else
                {
                    throw new Exception("ไม่มีข้อมูลรายการพัสดุที่เลือก เนื่องจากอาจมีรวบรวมรายการซ้ำซ้อน");
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
        }

        public void InsertItem(int PrSeq, string PprItemSeq, Nullable<int> BgmExpSeq, Nullable<int> PcRuleSeq, Nullable<int> ItemSeq, int prEmpSeq, IDBManager dbManagerTrans)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            int tmpPprItemSeq, tmpItemSeq, tmpItemQty;
            string prPprItemSeq = "", tmpItemDescr = "";
            double tmpItemPrice = 0;
            double prItemMinPrice = 0, prItemMaxPrice = 0;
            int prItemQty = 0;
            string prItemDescr = "";

            try
            {
                dbManagerTrans.CreateParameters(3);

                dbManagerTrans.AddParameters(0, "@bgm_exp_seq", BgmExpSeq);
                dbManagerTrans.AddParameters(1, "@pc_rule_seq", PcRuleSeq);
                dbManagerTrans.AddParameters(2, "@item_seq", ItemSeq);

                strSQL = "SELECT *"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM"
                        + " WHERE ICM_PPR_ITEM_SEQ IN (" + PprItemSeq + ")"
                            + " AND ICM_PR_ITEM_SEQ IS NULL"
                            + " AND BGM_EXP_SEQ = @bgm_exp_seq"
                            + " AND ICM_PC_RULE_SEQ = @pc_rule_seq"
                            + " AND ICM_RES_ITEM_SEQ = @item_seq";

                ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        tmpPprItemSeq = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_SEQ"]) ? 0 : (int)(dr[i]["ICM_PPR_ITEM_SEQ"]));
                        tmpItemSeq = (Convert.IsDBNull(dr[i]["ICM_RES_ITEM_SEQ"]) ? 0 : (int)(dr[i]["ICM_RES_ITEM_SEQ"]));
                        tmpItemQty = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_QTY"]) ? 0 : (int)(dr[i]["ICM_PPR_ITEM_QTY"]));
                        tmpItemPrice = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_PRICE"]) ? 0 : (double)(dr[i]["ICM_PPR_ITEM_PRICE"]));
                        tmpItemDescr = (dr[i]["ICM_PPR_ITEM_DESCR"]).ToString();

                        // ----- set value to insert into icm_purchase_request_item
                        prPprItemSeq = prPprItemSeq + tmpPprItemSeq + ";";
                        prItemQty = prItemQty + tmpItemQty;
                        if (tmpItemDescr.Length > 0)
                        {
                            prItemDescr = prItemDescr + tmpItemDescr + " ,";
                        }

                        if (i == 0)
                        {
                            prItemMinPrice = tmpItemPrice;
                            prItemMaxPrice = tmpItemPrice;
                        }
                        else
                        {
                            if (tmpItemPrice < prItemMinPrice)
                            {
                                prItemMinPrice = tmpItemPrice;
                            }
                            if (tmpItemPrice > prItemMaxPrice)
                            {
                                prItemMaxPrice = tmpItemPrice;
                            }
                        }
                    }
                    prPprItemSeq = prPprItemSeq.Substring(0, prPprItemSeq.Length - 1);
                    if (prItemDescr.Length > 0)
                    {
                        prItemDescr = prItemDescr.Substring(0, prItemDescr.Length - 1);
                    }

                    dbManagerTrans.CreateParameters(9);

                    dbManagerTrans.AddParameters(0, "@ppr_item_seq_text", prPprItemSeq);
                    dbManagerTrans.AddParameters(1, "@pr_seq", PrSeq);
                    dbManagerTrans.AddParameters(2, "@exp_seq", BgmExpSeq);
                    dbManagerTrans.AddParameters(3, "@item_seq", ItemSeq);
                    dbManagerTrans.AddParameters(4, "@item_qty", prItemQty);
                    dbManagerTrans.AddParameters(5, "@item_min_price", prItemMinPrice);
                    dbManagerTrans.AddParameters(6, "@item_max_price", prItemMaxPrice);
                    dbManagerTrans.AddParameters(7, "@item_descr", prItemDescr);
                    dbManagerTrans.AddParameters(8, "@pr_emp_seq", prEmpSeq);

                    strSQL = "CALL P_Auto_Insert_Purchase_Request_Item(@ppr_item_seq_text, @pr_seq"
                        + " , @exp_seq, @item_seq, @item_qty, @item_min_price, @item_max_price, @item_descr"
                        + " , @pr_emp_seq)";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
        }
    }
}
